package us.quizgame.database;

import java.sql.*;

public class Database
{
	private static String url = "jdbc:mysql://admin.quizgame.us/";
	private static String dbName = "quizgame";
	private static String driver = "com.mysql.jdbc.Driver";
	private static String userName = "root";
	private static String password = "Letmein22.com";
	private static Connection con = null;
	
	private void connectDatabase() throws Exception
	{
		Class.forName(driver).newInstance();
		con = DriverManager.getConnection(url + dbName, userName, password);
		System.out.println("Connected to the database");	
	}
	
	private void closeDatabase() throws Exception
	{
		con.close();
		System.out.println("Disconnected from database");
	}


	public Question[] fetchQuestions(int numOfQuestions) 
	{
		
		Question[] questions = new Question[numOfQuestions];
		int counter = 0;
		
		try
		{
			connectDatabase();
			
			Statement statement = con.createStatement();
			ResultSet results = statement.executeQuery("SELECT * FROM questions WHERE valid=1 ORDER BY RAND() LIMIT " + numOfQuestions);
			
			while (results.next())
			{
				questions[counter] = new Question(results.getString("question"), results.getString("righta"), 
						results.getString("wronga"));
				counter++;				
			}
			
			closeDatabase();
		} 
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		return questions;
	}
	
	public void inputQuestion(String question, String correctAnswer, String wrongAnswer)
	{
		try
		{
			connectDatabase();
			
			PreparedStatement stmt = con.prepareStatement("INSERT INTO questions (question, righta, wronga, valid) VALUES(?,?,?,?)");
			stmt.setString(1, question);
			stmt.setString(2, correctAnswer);
			stmt.setString(3, wrongAnswer);
			stmt.setInt(4, 0);
			stmt.execute();
			
			closeDatabase();
		} 
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
	}
	
	public void inputScore(String name, int score, double time, String gameMode)
	{
		try
		{
			connectDatabase();
			
			PreparedStatement stmt = con.prepareStatement("INSERT INTO " + gameMode + "scores (name, score, time) VALUES(?,?,?)");
			stmt.setString(1, name);
			stmt.setInt(2, score);
			stmt.setDouble(3, time);
			stmt.execute();
			
			closeDatabase();
		} 
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
	
	public Score[] fetchTopScores(int numOfScores, String gameMode)
	{
		Score[] scores = new Score[numOfScores];
		int counter = 0;
		
		try
		{
			connectDatabase();
			
			Statement statement = con.createStatement();
			ResultSet results = statement.executeQuery("SELECT * FROM " + gameMode + "scores ORDER BY score DESC, time LIMIT " + numOfScores);
			
			while (results.next())
			{
				scores[counter] = new Score(results.getString("name"), results.getInt("score"), 
						results.getDouble("time"));
				counter++;				
			}
			
			closeDatabase();
		} 
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		return scores;
	}
}
